<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzb.erp.api.pc.clazz.mapper.ClassStudentMapper">

    <select id="signRecord" resultType="com.hzb.erp.api.pc.clazz.pojo.ClassStudentSignVO">
        select s.*, t2.id,t2.sign_time, t2.sign_state, t2.sign_type, t2.dec_lesson_count, t2.lesson_count, t3.name
        student_name,t5.mobile,
        t7.course_id consume_course_id, t8.name consume_course_name,
        t2.class_id, t6.name class_name,
        (sum(t7.count_lesson_total - t7.count_lesson_complete - t7.count_lesson_refund)) count_lesson_remaining
        from (
            (
                SELECT student_id, lesson_id FROM lesson_student where lesson_id = #{param.lessonId}
            ) UNION (
                SELECT t1.student_id, t2.id lesson_id FROM class_student t1
                LEFT JOIN lesson t2 on t2.class_id = t1.class_id
                where t2.id = #{param.lessonId} and t1.deleted = 0 and t2.state != 2
            )
        ) s
        LEFT JOIN lesson_student t2 on t2.lesson_id = s.lesson_id and t2.student_id = s.student_id
        LEFT JOIN student t3 on t3.id = s.student_id
        LEFT JOIN class_student t4 on t4.class_id = t2.class_id and t4.student_id = s.student_id and t4.deleted = 0
        LEFT JOIN user t5 on t5.id = t3.user_id
        LEFT JOIN class t6 on t6.id = t2.class_id
        LEFT JOIN student_course t7 ON t7.student_id = s.student_id
            AND t7.course_id = IFNULL(t2.consume_course_id, t4.consume_course_id)
            AND t7.deleted = 0
        LEFT JOIN course t8 on t8.id = t7.course_id
        <if test="param.unsigned != null and param.unsigned == true">
            WHERE t2.sign_time is null
        </if>
        GROUP BY s.student_id
    </select>

    <select id="selectClassStudentsByLessonId" resultType="com.hzb.erp.api.pc.clazz.entity.ClassStudent">
        SELECT t1.*
        FROM class_student t1
        <if test="all == false">
            LEFT JOIN lesson_student t2 ON t2.lesson_id = #{lessonId} and t2.student_id = t1.student_id and t2.class_id
            = t1.class_id
        </if>
        WHERE
        t1.deleted = 0
        and t1.class_id = ( SELECT class_id FROM lesson WHERE id = #{lessonId})
        <if test="all == false">
            and t2.id is null
        </if>
    </select>
    <select id="listNoLessonStudent" resultType="com.hzb.erp.api.pc.clazz.entity.ClassStudent">
        SELECT DISTINCT
            t1.*
        FROM
            class_student t1
                LEFT JOIN lesson_student t2 ON t2.student_id = t1.student_id AND t2.class_id = t1.class_id
                LEFT JOIN class t3 ON t3.id = t1.class_id
                left join lesson t4 on t4.class_id = t1.class_id
        WHERE
            t1.deleted = 0
          AND t3.deleted = 0
          AND (t3.be_over = 0 OR t3.end_date &gt;= CURDATE())
          AND t4.date &gt;= CURDATE()
          AND t2.id IS NULL
        GROUP BY t1.id having count(t4.id) &gt; 0
    </select>

</mapper>
